Use Smart Integration Connector

You can use Smart Integration Connector to access data from your Local Gateway Connection Data Source or through Direct Connections.

Examples

Data Adapters Example

  1. Go to Application > Presentation > Dashboards > Workspaces > [choose Workspace] > [choose Maintenance Unit] > Data Adapters.

  2. Create or select an existing data adapter.

  3. Verify that the Database Location is External and the External Database Connection is the custom connection that you defined earlier.

  4. Enter a valid SQL Query.


  5. Test the data adapter and view the results.

SQL Table Editor Example

The following use case describes how to send a query after establishing a connection.

  1. Go to Application > Presentation > Dashboards > Workspaces > [choose Workspace] > [choose Maintenance Unit] > [choose Maintenance Unit] > Components > SQL Table Editor.

  2. Create or open a SQL Table Editor.

  3. Verify the following:

    • Database Location is External,

    • External Database Connection is the custom connection that you defined earlier,

    • Table Name is defined as the table you want to return data from.


  4. Open the associated dashboard and run the query. The OneStream Smart Integration Connector will connect to the external database. If it connects correctly, the query will populate.

Grid View Example

  1. Go to Application > Presentation > Dashboards > Workspaces > [choose Workspace] > [choose Maintenance Unit] > [choose Maintenance Unit] > Components > Grid View.

  2. Create or open a grid view.

  3. Configure the grid to use the data adapter.

  4. Run the associated dashboard to see the data.

Perform a Drill Back

The following snippet describes how to load data from a local gateway connection data source and how to perform a drill back. The example below has been updated from the Standard SQL Connectors business rule. If you do not have the Snippet Editor with the OneStream Application, you can find the Snippet Editor on the MarketPlace.

  1. Download the Snippet Editor from the MarketPlace.

  2. Navigate to Application > Tools > Business Rules.

  3. Open Connector.

  4. Navigate to Snippets > SQL Connector > Standard SQL Connectors.

  5. Copy the Sample Business Rule.

  6. Edit the query information. Enter dim ConnectionStringGateway As String = Your Connection information.

    NOTE: This example assumes that you have completed the setup and installation process and configured a custom database connection in the System Configuration as a Gateway type. Refer to Define Database Location in OneStream for more information.

    Copy 
    'Get the query information (prior to using the gateway)
    Dim connectionstring As String = GetConnectionString(si, globals, api)
    'Get the query information (using the gateway)
    Dim connectionString_gateway As String = GetConnectionString_Gateway(si, global3, api)|

  7. Enter the connection name. In this example, “Northeast Sales” is the Gateway Connection Name as defined in the application configuration.

    Copy 
    'Create a Connection string to the External Database (prior to using the gateway)
    Private Function GetConnectionString(ByVal si As Sessioninfo, ByVal globals As BRGlobals, ByVal api As Transformer) As String 
      Try
        'Named External Connection
        '-------------------------------------------
        Return "Revenue Mgmt System"
      Catch ex As Exception
        Throw ErrorHandler.LogWrite(si, New XFException(si, ex))
      End Try 
    End Function

    'Create a Connection string to the External Database (using the Gateway)
    Private Function GetConnectionStringGateway(ByVal si As Sessioninfo, ByVal globais As BRGlobals, ByVal api As Transformer) As String
        Try
          'Named External Connection - Gateway
          '---------------------------------------------
          Return "Northeast Sales"
        Catch ex As Exception
            Throw ErrorHandler.LogWrite(si, New XFException(si, ex)) 
        End Try 
    End Function

  8. Enter the drill back information to your database.

    Copy 
    If args.DrillCode.Equals(StageConstants.TransformationGeneral.DrillCodeDefaultValue, StringComparison.InvariantCulturelgnoreCase) Then
       'Source GL Drill Down
       drillTypes.Add(New DrillBackTypeInfo(ConnectorDrillBackDisplayTypes.FileShareFile, New NameAndDesc("InvoiceDocument","Invoice Document"))) 
       drillTypes.Add(New DrillBackTypeInfo(ConnectorDrillBackDisplayTypes.DataGrid, New NameAndDesc("MaterialTypeDetail","Material Type Detail")))
       drillTypes.Add(New DrillBackTypeInfo(ConnectorDrillBackDisplayTypes.DataGrid, New NameAndDesc("MaterialTypeDetail_Gateway","Material Type Detail (Smart Integration)")))

  9. Edit the level of drill back information returned.

    Example: This example shows previously existing code that leverages a VPN based SQL connection and the Gateway based method shown in the second "Else If" block.

Copy 
Else If args.DrillBackType.NameAndDescription.Name.Equals("MaterialTypeDetail", StringComparison.InvariantCultureIgnoreCase) Then
  'Level 1: Return Drill Back Detail
  Dim dri1lBackSQL As String - GetDrillBackSQL_Ll(si, globais, api, args)
  Dim drillBackInfo As New DrillBackResultInfo
  drillBackInfo.DisplayType = ConnectorDrillBackDisplayTypes.OataGrid
  drillBackInfo.DataTable = api.Parser.GetXFDataTableForSQLQuery(si, DbProviderType.SqlServer, connectionstring. True, drillBackSQL, False, args.PageSize, args.PageNumber) 
  Return drillBacklnfo

Else If args.DrillBackType.NameAndDescription.Name.Equals("MaterialTypeDetail_Gateway", StringComparison.lnvariantCultureIgnoreCase) Then 
  'Level 1: Return Drill Back Detail
  Dim drillBackSQL As String = GetDrillBackSQL_Ll(si, globais, api, args)
  Dim drillBackInfo As New DrillBackResultInfo
  drillBackInfo.DisplayType = ConnectorDrillBackDisplayTypes.OataGrid
  drillBackInfo.DataTable = api.Parser.GetXFDataTableForSQLQuery(si, DbProviderType.Gateway, connectionstring_gateway. True, drillBackSQL, False, args.PageSize, args.PageNumber) 
  Return drillBacklnfo

Perform a Write Back

You can perform a write back using Smart Integration Connector leveraging the defined credentials to the local gateway datasource at the Smart Integration Connector Gateway. If the credentials have permission to insert, update, and/or delete records in a remote datasource, a OneStream business rule could be leveraged to write-back, update, and/or delete data as needed to support a financial process.

Example: The following example shows how to insert rows and columns to a Smart Integration Connector remote database.

Copy 

                Imports System
Imports System.Collections.Generic
Imports System.Data
Imports System.Data.Common
Imports System.Globalization
Imports System.IO
Imports System.Linq
Imports System.Windows.Forms
Imports Microsoft.VisualBasic
Imports OneStream.Finance.Database
Imports OneStream.Finance.Engine
Imports OneStream.Shared.Common
Imports OneStream.Shared.Database
Imports OneStream.Shared.Engine
Imports OneStream.Shared.Wcf
Imports OneStream.Stage.Database
Imports OneStream.Stage.Engine

Namespace OneStream.BusinessRule.Extender.SIC_BulkCopyExample
    Public Class MainClass
        Public Function Main(ByVal si As SessionInfo, ByVal globals As BRGlobals, ByVal api As Object, ByVal args As ExtenderArgs) As Object
            Try
                ' SIC Gateway name
                Dim sicGatewayName As String = "jl-db-achqa1-gateway"
                
                ' SIC remote rule
                Dim sicRemoteRule As String = "SIC_Functions"
                
                ' SIC remote rule function
                Dim sicRemoteRuleFunction As String = "RunOperation"

                ' Create and populate DataTable
                Dim dt As New DataTable()
                dt.Columns.Add("Scenario", GetType(String))
                dt.Columns.Add("Time", GetType(String))                
                dt.Columns.Add("Entity", GetType(String))
                dt.Columns.Add("Account", GetType(String))
                dt.Columns.Add("Amount", GetType(Double))
                dt.Rows.Add("Actual", "2023M3", "Houston Heights", "Net Sales", 100.25)
                dt.Rows.Add("Actual", "2023M3", "South Houston", "Net Sales", 1230.66)                
                
                ' Compress data table before passing into remote business rule
                Dim dtCompress As CompressionResult  = CompressionHelper.CompressJsonObject
                (Of DataTable)(si, dt, XFCompressionAlgorithm.DeflateStream)                
                
                Dim dtObj(2) As Object ' Create object to store arguments for remote business rule
                dtObj(0) = dtCompress ' compressed datatable
                dtObj(1) = "SIC_WriteBack" ' remote database table name
                dtObj(2) = "RevenueMgmt" ' remote data source name                            
                
                ' Execute remote business rule to bulk copy to target table
                Dim bulkRemoteResults As RemoteRequestResultDto 
                =BRApi.Utilities.ExecRemoteGatewayBusinessRule(si, sicRemoteRule, 
                dtObj, sicGatewayName,sicRemoteRuleFunction,String.Empty, False, 600)
                
                ' Get result status
                If bulkRemoteResults.RemoteResultStatus <> 
                RemoteMessageResultType.RunOperationReturnObject Then ' Check if successful
                    ' Failed, do something
                    BRAPi.ErrorLog.LogMessage(si,"Failed with status:" & bulkRemoteResults.
                    RemoteResultStatus.ToString)
                    
                End If
                
                ' Get returned message
                Dim returnedMsg As String = CompressionHelper.InflateJsonObject(Of String)
                (si,bulkRemoteResults.resultDataCompressed)                
                
                BRAPi.ErrorLog.LogMessage(si,returnedMsg)
                
                Return Nothing
            Catch ex As Exception
                Throw ErrorHandler.LogWrite(si, New XFException(si, ex))
            End Try
        End Function
    End Class
End Namespace

The Extensibility Rule above calls the following Smart Integration Function:

Copy 
Imports System
Imports System.Collections.Generic
Imports System.Data
Imports System.Data.Common
Imports System.Globalization
Imports System.IO
Imports System.Linq
Imports System.Data.SqlClient
Imports OneStream.Shared.Common
Imports OneStreamGatewayService

Namespace OneStream.BusinessRule.SmartIntegrationFunction.SIC_Functions
    Public Class MainClass
        
        ' Function to bulk copy a compressed data table to a SQL database table
        ' Pass in compressed data table, database table name and data source name
        Public Shared Function RunOperation(dtCompress As CompressionResult,tablename As String, 
        datasource As String) As String

            ' -----------------------------------------------------------------------------------------------
            ' Get SQL connection string
            Dim connString As String = APILibrary.GetRemoteDataSourceConnection(datasource)
        
            ' Inflate compressed datatable
            Dim dt As DataTable = CompressionHelper.InflateJsonObject(Of DataTable)
            (New SessionInfo,dtCompress)
            
            If dt IsNot Nothing AndAlso dt.Rows.Count > 0 Then 
            ' Check data table has been created and is populated
            
                ' Create sql connection to DWH
                Using sqlTargetConn As SqlConnection = New SqlConnection(connString)
                
                    sqlTargetConn.Open ' Open connection
                    
                    Using bulkCopy = New SqlBulkCopy(sqlTargetConn)
                    
                        bulkCopy.DestinationTableName = tableName ' DWH table
                        bulkCopy.BatchSize = 5000
                        bulkCopy.BulkCopyTimeout = 30
                        
                        bulkCopy.WriteToServer(dt) ' Bulk copy data table to database table
                    
                    End Using 
                
                End Using    
                
            Else
                Throw New Exception("Problem uncompressing data in SIC gateway")
            End If
                
            Return $"{dt.Rows.Count} rows bulk inserted into table {tableName}"
            
        End Function        
        
    End Class
End Namespace

Support for sFTP

Smart Integration Connector provides support for connecting to sFTP servers to send and retrieve files. Perform the steps in the following sections to establish a connection and then send and retrieve files.

NOTE: You must have an sFTP server available on a port. The port must be allowed for inbound and outbound connections on the Local Gateway Server. For this example, we have used port 22.

  1. Login to OneStream.

  2. Navigate to System > Administration > Smart Integration Connector.

  3. Create a New Gateway and fill out all of the corresponding details for your Gateway and the Gateway Server.

  4. From Connection Type, select Direct Connection (e.g., SFTP, WebAPI).

  5. For Bound Port at Gateway, enter 22.

  6. For Remote Gateway Host, enter the IP address or resolvable host name of the machine where your SFTP server is located.


  7. For Bound Port in OneStream, enter -1 to automatically assign an unused port number. You can also specify your own port number by entering a value greater than 1024 and less than 65535. It is a best practice to use a higher value because it is less likely that number will be in use as this port number must be globally unique across all applications hosted on the OneStream servers.

  8. Click OK.

  9. Copy the Gateway to the OneStream Smart Integration Connector Local Gateway Server Configuration.

  10. Save the Local Gateway Server configuration and restart the Smart Integration Connector Gateway service.

Example: Here is an example of how you can upload and download files through an SFTP extensibility rule.

NOTE: You will need to add WinSCPnet.DLL to your business rule Referenced Assemblies from the Properties tab in the business rule.

Copy 
Imports System
Imports System.Collections.Generic
Imports System.Data
Imports System.Data.Common
Imports System.Globalization
Imports System.IO
Imports System.Linq
Imports System.Windows.Forms
Imports Microsoft.VisualBasic
Imports OneStream.Finance.Database
Imports OneStream.Finance.Engine
Imports OneStream.Shared.Common
Imports OneStream.Shared.Database
Imports OneStream.Shared.Engine
Imports OneStream.Shared.Wcf
Imports OneStream.Stage.Database
Imports OneStream.Stage.Engine
Imports WinSCP
 

Namespace OneStream.BusinessRule.Extender.SFTP_Example
    Public Class MainClass
        Public Function Main(ByVal si As SessionInfo, ByVal globals As BRGlobals, ByVal api As Object, ByVal args As ExtenderArgs) As Object
            
        Try
            
            ' Setup the objects to read Gateway Details from BRAPIs
            Dim objGatewayDetails As GatewayDetails = BRApi.Utilities.GetGatewayConnectionInfo(si, "WinSCP_Gateway")
            Dim objRemoteRequestResultDto As RemoteRequestResultDto = BRApi.Utilities.ExecRemoteGatewayBusinessRule(si, "SFTP_Password", Nothing, "rochester_gateway",String.Empty,"SFTP_Password", False, 600)
            
            ' Setup session options
            Dim sessionOptions As New SessionOptions
            With sessionOptions
                .Protocol = Protocol.Sftp
                .HostName = "localhost"           'HostName in this instance is in refrence to OneStream and will always be localhost.
                .UserName = "onestreamtest"       'sFTP server UserName
                '.Password = "**********"       'sFTP server Password
                .Password = objRemoteRequestResultDto.ObjectResult      ' This is the returned value from the remote rule that obtains the customer controlled password
                .PortNumber = objGatewayDetails.OneStreamPortNumber     
'use BRAPI to populate Port Number and return the dynamically assigned value from OneStream
                .SshHostKeyFingerprint = "*****************************"  'SSH Host Key from sFTP host 
            End With
             
            Using session As New Session
                ' Connect
                session.Open(sessionOptions)
 
                ' Get the filepath
                ' BatchHarvest in this example is File Share / Applicaitons / GolfStream / Batch / Harvest
                Dim fileUPPath As String = BRAPi.Utilities.GetFileShareFolder(si, FileShareFolderTypes.BatchHarvest, Nothing)
                Dim fileDNPath As String = BRAPi.Utilities.GetFileShareFolder(si, FileShareFolderTypes.BatchHarvest, Nothing)
                                
                ' Upload or download files
                Dim transferOptions As New TransferOptions
                transferOptions.TransferMode = TransferMode.Binary
 
                Dim transferResult As TransferOperationResult
                ' Upload
                fileUPpath = fileUPPath & "\SFTP_TEST_UPLOAD.txt"
                transferResult = session.PutFiles(fileUPpath, "/", False, transferOptions)
                
                'Throw on any error
                transferResult.Check()
                
                ' Download
                fileDNpath = fileDNPath & "\SFTP_TEST_DOWNLOAD.txt"
                transferResult = session.GetFiles("\SFTP_TEST_DOWNLOAD.txt", fileDNpath, False, transferOptions)
                
                'Throw on any error
                transferResult.Check()
              
            End Using
 
            Return Nothing
        Catch ex As Exception
            Throw ErrorHandler.LogWrite(si, New XFException(si, ex))
            Return Nothing
        End Try
        
        End Function
    End Class
End Namespace

Transferring Files from Local FileShare

You can use a Data Management job to move files Smart Integration Connector from a local FileShare. To do this, you build an extender business rule and call it through a data management job. This extender business rule will call a Smart Integration Function (remote function) and obtain the results.

Step 1 - Setup the Remote Server / Remote Share

To get started, setup the Smart Integration Function:

  1. Navigate to Application > Tools > Business Rules.

  2. Open the Smart Integration Function folder.

  3. Create a new business rule (for example, TestFileRead) .

  4. Copy and paste the following business rule code snippet.

Copy 
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Globalization;
using System.IO;
using System.Linq;

namespace OneStream.BusinessRule.SmartIntegrationFunction.TestFileRead
{
       public class MainClass
       {     
              public byte[] RunOperation(string year)
              {
                     string fname = @"c:\temp\hw_" +  year + ".csv";
                     byte[] buffer = System.IO.File.ReadAllBytes(fname);                                   
                     return buffer;
              }

              public byte[] GetOtherFileData(string year)
              {
                     string fname = @"c:\temp\zw_" +  year + ".csv";
                     byte[] buffer = System.IO.File.ReadAllBytes(fname);                                   
                     return buffer;
              }

              public bool DeleteOldFileData(string year)
              {
                     string fname = @"c:\temp\zw_" +  year + ".csv";
                     try
                     {
                       System.IO.File.Delete(fname);
                       return true;
                     }
                     catch (IOException)
                     {
                           return false;
                     }
              }            
       }
}

Step 2 - Pull file from Extender Business Rule

  1. Navigate to Application > Tools > Business Rules.

  2. Open the Extensibility Rules folder.

  3. Create a new business rule (for example, ProcessRemoteFileData) .

  4. Copy and paste the following business rule code snippet.

    Copy 
    Imports System
    Imports System.Data
    Imports System.Data.Common
    Imports System.IO
    Imports System.Collections.Generic
    Imports System.Globalization
    Imports System.Linq
    Imports Microsoft.VisualBasic
    Imports System.Windows.Forms
    Imports OneStream.Shared.Common
    Imports OneStream.Shared.Wcf
    Imports OneStream.Shared.Engine
    Imports OneStream.Shared.Database
    Imports OneStream.Stage.Engine
    Imports OneStream.Stage.Database
    Imports OneStream.Finance.Engine
    Imports OneStream.Finance.Database

    Namespace OneStream.BusinessRule.Extender.ProcessRemoteFileData
        Public Class MainClass
            Public Function Main(ByVal si As SessionInfo, ByVal globals As BRGlobals, ByVal api As Object, ByVal args As ExtenderArgs) As Object
                Try
                    Dim stepNumber As String = "1"
                    
                    If (Not args.NameValuePairs Is Nothing) Then
                      ' Extracting the value from the parameters collection                  
                      If (args.NameValuePairs.Keys.Contains("step")) Then
                          stepNumber = args.NameValuePairs.Item("step")
                      End If 
                      BRApi.ErrorLog.LogMessage(si, "File Processing Step: " & stepNumber) 
                    End If 
                    
                    Select Case stepNumber
                        
                        Case Is = "1"
                            GetData(si)
                            Return Nothing
                        
                        Case Is = "2"
                            CleanupData(si)
                            Return Nothing 
                            
                                                 
                    End Select    
                    
                    
                Catch ex As Exception
                    Throw ErrorHandler.LogWrite(si, New XFException(si, ex))
                End Try
                
                Return Nothing
            End Function

                
            Public Sub CleanupData(ByVal si As SessionInfo)
                
                    Dim argTest(0) As Object                         
                    argTest(0) = "2023"
                    
                    ' Here we are telling it to specifically call 
                     Dim objRemoteRequestResultDto As RemoteRequestResultDto = BRApi.Utilities.ExecRemoteGatewayBusinessRule(si, "TestFileRead", argTest, "gateway-jasonl-smartic", "DeleteOldFileData")
                    If (objRemoteRequestResultDto.RemoteResultStatus = RemoteMessageResultType.RunOperationReturnObject) Then
                        
                        ' The delete method returns a true/false return type
                        Dim result As Boolean
                        ' ObjectResultValue introduced in v7.4 to simplify obtaining the return value from a method that doesn't return a 
                        ' Dataset/Datatable
                        result = objRemoteRequestResultDto.ObjectResultValue
                        
                        Dim objRemoteRequestResultDtoCached As RemoteRequestResultDto = BRApi.Utilities.ExecRemoteGatewayCachedBusinessRule(si, "TestFileReadCache", argTest, "gateway-jasonl-smartic", String.Empty)                    
                        
                        BRApi.ErrorLog.LogMessage(si, "File Deleted: " & result) 
                    Else
                      If (Not (objRemoteRequestResultDto.remoteException Is Nothing)) Then
                             Throw ErrorHandler.LogWrite(si, New XFException(si, objRemoteRequestResultDto.remoteException))
                      End If 
                  End If

            End Sub
            
            Public Sub GetData(ByVal si As SessionInfo)
                    
                    ' Demonstrating how to pass parameters
                    ' We create an object array that matches the number of parameters
                    ' To the remote function.  In this case, we have 1 parameter that is a string
                    Dim argTest(0) As Object                         
                    argTest(0) = "2023"
                    
                    ' This is where you can allow caching of the remote function.  We are passing in true at the end to force the cache to be updated
                    ' We are also allowing the function to run for 90 seconds.
                    ' String.empty means this will look for a remote function/method called "RunOperation"
                     Dim objRemoteRequestResultDto As RemoteRequestResultDto = BRApi.Utilities.ExecRemoteGatewayBusinessRule(si, "TestFileRead", argTest, "ryantestconnection2", String.Empty,"TestFileRead", True, 90)
                    If (objRemoteRequestResultDto.RemoteResultStatus = RemoteMessageResultType.RunOperationReturnObject) Then
                        Dim bytesFromFile As Byte()             
                        bytesFromFile = objRemoteRequestResultDto.ObjectResultValue
                        Dim valueAsString As String = System.Text.Encoding.UTF8.GetString(bytesFromFile)
                        Return valueAsString
                        bytesFromFile = Convert.FromBase64String(objRemoteRequestResultDto.ObjectResultValue)
                        'bytesFromFile = objRemoteRequestResultDto.ObjectResultValue
                        
                        
                        Dim valueAsString As String = System.Text.Encoding.UTF8.GetString(bytesFromFile)
                    
                          ' Do something with the files here....
                        BRApi.ErrorLog.LogMessage(si, "File Contents: " & Left(valueAsString,10)) 
                        ' We are saving the file into the OneStream Share here
                        ' This is an option to allow other OneStream functions to process the data
                        'Dim groupFolderPath As String = FileShareFolderHelper.GetGroupsFolderForApp(si, True, AppServerConfig.GetSettings(si).FileShareRootFolder, si.AppToken.AppName)
                        Dim groupFolderPath As String = BRAPi.Utilities.GetFileShareFolder(si, FileShareFolderTypes.BatchHarvest, Nothing)
                        Using sw As StreamWriter = New StreamWriter(groupFolderPath & "\outputfile.csv")
                          sw.Write(valueAsString)
                          sw.Close()
                        End Using
                    Else
                      If (Not (objRemoteRequestResultDto.remoteException Is Nothing)) Then
                             Throw ErrorHandler.LogWrite(si, New XFException(si, objRemoteRequestResultDto.remoteException))
                      End If 
                  End If
            End Sub    
            
        End Class
    End Namespace

  5. Test your Extender Business Rule via the Execute Extender button in the toolbar.

Step 3 - Automate from Data Management / Task Scheduler

After the Extensibility Rule has been created and tested you can automate from a Data Management Job and associate Task Schedule. See Task Scheduler for more information.

  1. Navigate to Application > Tools > Data Management.

  2. Create a new Data Management Group.

  3. Enter the business rule.

  4. Set the first Parameter to step=1.

  5. Set the Parameters to step=2.

  6. Create associated Task Schedule to run the Data Management job.

Obtain Data through a WebAPI

In this scenario, you have a WebAPI (IPaaS integration or another accessible REST API) to obtain and pass back data to OneStream. You can use the following remote business rule in Smart Integration Connector to invoke the API. If you have results that are in JSON format, you can convert them to a data table and send them back to OneStream. If the data from the WebAPI is in JSON, you can process the data in Smart Integrator Connector. Additionally, you can send the raw data back as a string to a data management job for further testing.

Direct connections are preferred for this method and can be invoked using business rules within OneStream similar to the sFTP example provided above.

See Multiple WebAPI Connections for best practices on scenarios with multiple WebAPIs.

Single WebAPI Connection

To set up a single WebAPI connection:

  1. Set up a Direct Connection Gateway.

  2. Export the Configuration and import to your Local Gateway Server. See the Export and Import the Gateway Configuration section for more information on this process.

  3. Refresh your Gateways and verify this new Gateway is online.

    IMPORTANT: Copy your Bound Port in OneStream. You will reference this later in the extensibility rule.

  4. Create the Extensibility Rule below:

    Copy 
    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.Common;
    using System.Globalization;
    using System.IO;
    using System.Linq;
    using OneStream.Shared.Common;
    using OneStream.Shared.Database;
    using OneStream.Shared.Engine;
    using OneStream.Shared.Wcf;
    using System.Net;
    using System.Net.Http;
    using Newtonsoft.Json;
    using System.Net.Http.Headers;


    namespace OneStream.BusinessRule.Extender.SIC_WebAPI
    {
        public class MainClass
        {
            private static readonly HttpClient internalHttpClient = new HttpClient();
        
            public object Main(SessionInfo si, BRGlobals globals, object api, ExtenderArgs args)
            {
                try
                {
                    internalHttpClient.DefaultRequestHeaders.Accept.Clear();
                       internalHttpClient.DefaultRequestHeaders.Accept.Add(new MediaTypeWithQualityHeaderValue("application/json"));
                    internalHttpClient.DefaultRequestHeaders.Accept.Add(new MediaTypeWithQualityHeaderValue("application/x-www-form-urlencoded"));
                    internalHttpClient.DefaultRequestHeaders.Accept.Add(new MediaTypeWithQualityHeaderValue("application/octet-stream"));
                    internalHttpClient.DefaultRequestHeaders.Accept.Add(new MediaTypeWithQualityHeaderValue("text/plain"));
                    internalHttpClient.DefaultRequestHeaders.Accept.Add(new MediaTypeWithQualityHeaderValue("*/*"));
                
                    // The header must be set or some connections maybe refused.    
                    internalHttpClient.DefaultRequestHeaders.Host = "api.open-meteo.com";
                    
                    // In this example, 20540 is the Bound Port in OneStream for the Gateway being used.            
                    var stringTask = internalHttpClient.GetStringAsync("https://localhost:20540/v1/forecast?latitude=40.73&longitude=-73.94&daily=temperature_2m_max,temperature_2m_min&temperature_unit=fahrenheit&timezone=America%2FNew_York");
                    
                    // Display the result in the exception dialog as an example.
                    throw new Exception(stringTask.Result);        
                }
                catch (Exception ex)
                {
                    throw ErrorHandler.LogWrite(si, new XFException(si, ex));
                }
            }
        }
    }

  5. Compile and test the business rule. If the extensibility ran successfully, you should see the correct data that corresponds with the business rule in the Exception dialog box:

Multiple WebAPI Connections

If you are using more than one WebAPI, the best practice is to perform this process using a single Gateway and multiple remote Business Rules.

Use the following OneStream business rule to invoke the request.

Copy 
Dim objRemoteRequestResultDto As RemoteRequestResultDto = BRApi.Utilities.ExecRemoteGatewayBusinessRule(si, "RemoteWebAPISample", Nothing, "testconnection",String.Empty) If (objRemoteRequestResultDto.RemoteResultStatus = RemoteMessageResultType.Success) Dim xfDT = New XFDataTable(si,objRemoteRequestResultDto.resultSet,Nothing,1000) End If

Use the following remote business rule to execute the request in C#

Copy 
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Globalization;
using System.IO;
using System.Linq;
using System.Net;
using System.Net.Http;
using Newtonsoft.Json;
using System.Net.Http.Headers;

namespace OneStream.BusinessRule.SmartIntegrationFunction.RemoteWebAPISample
{
        public class MainClass
        {
               private static readonly HttpClient internalHttpClient = new HttpClient();
               
               static MainClass()
               {
               internalHttpClient.DefaultRequestHeaders.Accept.Clear();
            internalHttpClient.DefaultRequestHeaders.Accept.Add(new MediaTypeWithQualityHeaderValue("application/json"));
            internalHttpClient.DefaultRequestHeaders.Accept.Add(new MediaTypeWithQualityHeaderValue("application/x-www-form-urlencoded"));
            internalHttpClient.DefaultRequestHeaders.Accept.Add(new MediaTypeWithQualityHeaderValue("application/octet-stream"));
            internalHttpClient.DefaultRequestHeaders.Accept.Add(new MediaTypeWithQualityHeaderValue("text/plain"));
            internalHttpClient.DefaultRequestHeaders.Accept.Add(new MediaTypeWithQualityHeaderValue("*/*"));                       
               }
               
               public DataTable RunOperation() 
               {
                var stringTask = internalHttpClient.GetStringAsync(https://localhost:44388/WeatherForecast);

                       var msg = stringTask;
                       DataTable dt = (DataTable)JsonConvert.DeserializeObject(stringTask.Result, (typeof(DataTable)));

                       return dt;
               }
        }
}

Support for DLL Migration

For OneStream Platform version 8.0 and above, all customer-supplied DLLs will be referenced through Smart Integration Connector. To use a DLL, copy the DLLs to the Referenced Assemblies Folder in the Local Gateway Server Utility and reference this DLL within your Smart Integration Function. See Referenced Assemblies Folder.

To verify the Referenced Assemblies Folder path:

  1. Open the OneStream Local Gateway Configuration and Run as Administrator.

  2. Navigate to and open Local Application Data Settings.

  3. The file path under Referenced Assemblies Folder opens to the default location.

  4. Click the OK button.

See the following SAP example for this process in use. See Smart Integration Connector Settings for more information on these fields.

Support for ERPConnect (SAP)

As an alternative to creating a Local Gateway Connection to your SAP database, you can connect to SAP using third-party DLLs, such as ERPConnect##.dll. ERPConnect##.dll can be referenced using a Smart Integration Connector Remote business rule. Although ERPConnect45.dll can no longer enable a connection to SAP systems starting with Platform version 8.0, a newer version ERPConnectStandard20.dll is available through the download DLL Packages from the Platform page of the Solution Exchange. ERPConnect requires additional libraries to be obtained from SAP as well, which can reside in the same reference assembly folder as ERPConnect.

To get started:

  1. From the Platform page of the Solution Exchange, download the DLL Packages, which contains the ERPConnectStandard20.dll file.

  2. Copy the ERPConnectStandard20.dll to your Referenced Assemblies Folder.

  3. Install the required Visual C++ 2013 Runtime.

  4. From SAP, download and copy SAP NetWeaver RFC Library DLL (sapnwrfc.dll) and associated icudt50.dll, icuin50.dll, icuuc50.dll to your Referenced Assemblies Folder. See Theobald Software ERPConnect Requirements for additional information.

  5. Modify your business rules to use the ERPConnectStandard20.dll.

  6. Navigate to Application > Tools > Business Rules.

  7. Expand the Smart Integration Function list.

  8. Create a new Smart Integration Function or select an existing one.

  9. Click the Properties tab.

  1. Enter ERPConnectStandard20.dll in the Referenced Assemblies field.The Smart Integration Connector Gateway server will attempt to locate this DLL in the previously defined folder: Referenced BusinessRule AssemblyFolder.

  2. Add Imports for ERPConnect and ERPConnect.Utils.

     

  3. Verify you can compile the function on your Gateway.

You are now ready to add your custom code.